﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CMU.Certificates;

namespace CMU.Query
{
    public partial class MainWindow : Form
    {
        public MainWindow()
        {
            InitializeComponent();
            ConnectDB cn = new ConnectDB();


        }

        private void MainWindow_Load_1(object sender, EventArgs e)
        {
            CertificateEntities context = new CertificateEntities();
            var KH = from p in context.KhoaHoc select p;
            List<KhoaHoc> listKH = new List<KhoaHoc>(KH.ToList());
            KhoaHoc Khoahoc = new KhoaHoc();
            Khoahoc.Id = 0;
            listKH.Insert(0, Khoahoc);
            cmbKhoaHoc.DataSource = listKH;
            cmbKhoaHoc.DisplayMember = "TenKH";
            cmbKhoaHoc.ValueMember = "id";

            var NH = from p in context.ThoiGian select p;
            List<ThoiGian> listt = new List<ThoiGian>(NH.ToList());
            ThoiGian thoigian1 = new ThoiGian();
            thoigian1.Id = 0;
            listt.Insert(0, thoigian1);
            cmbNamHoc.DataSource = listt;
            cmbNamHoc.DisplayMember = "NamHoc";
            cmbNamHoc.ValueMember = "id";

            var MH = from p in context.MonHoc select p;
            List<MonHoc> listMH = new List<MonHoc>(MH.ToList());
            MonHoc monhoc = new MonHoc();
            monhoc.Id = 0;
            listMH.Insert(0, monhoc);
            cmbMonHoc.DataSource = listMH;
            cmbMonHoc.DisplayMember = "TenMH";
            cmbMonHoc.ValueMember = "id";


            var GV = from p in context.GiangVien select p;
            List<GiangVien> listGV = new List<GiangVien>(GV.ToList());
            GiangVien giangvien = new GiangVien();
            giangvien.Id = 0;
            listGV.Insert(0, giangvien);
            cmbGiangVien.DataSource = listGV;
            cmbGiangVien.DisplayMember = "TenGV";
            cmbGiangVien.ValueMember = "id";

            var SV = from p in context.SinhVien select p;
            List<SinhVien> listSV = new List<SinhVien>(SV.ToList());
            SinhVien sinhvien = new SinhVien();
            sinhvien.Id = 0;
            listSV.Insert(0, sinhvien);
            cmbSoCCCuaSV.DataSource = listSV;
            cmbSoCCCuaSV.DisplayMember = "HoVaTen";
            cmbSoCCCuaSV.ValueMember = "id";






        }
        private void button1_Click(object sender, EventArgs e)
        {
            int CCKhoaHoc = 0;
            CertificateEntities context = new CertificateEntities();
            context.ChungChi.Where(cc => cc.LopHoc.Id_GV == 1);
            //foreach (GiangVien gv in context.GiangVien)
            //    foreach (LopHoc lop in gv.LopHoc)
            //        MessageBox.Show(lop.MonHoc.TenMH);
            //thống kê chứng chỉ khóa hoc
            foreach (KhoaHoc kh in context.KhoaHoc)
            {
                if ((int)cmbKhoaHoc.SelectedValue != 0)
                {
                    foreach (LopHoc lop in kh.LopHoc)
                        foreach (ChungChi cc in lop.ChungChi)
                        {
                            if (lop.ChungChi != null && lop.Id_KH.Equals((int)cmbKhoaHoc.SelectedValue)) CCKhoaHoc++;
                        }

                }
            }


        }


        private void btnNamHoc_Click(object sender, EventArgs e)
        {
            int CCNamHoc = 0;
            CertificateEntities context = new CertificateEntities();
            foreach (ThoiGian tg in context.ThoiGian)
            {

                if ((int)cmbNamHoc.SelectedValue != 0)
                {
                    foreach (LopHoc lop in tg.LopHoc)
                    {
                        foreach (ChungChi cc in lop.ChungChi)
                        {
                            if (lop.ChungChi != null && lop.Id_TG.Equals((int)cmbNamHoc.SelectedValue)) CCNamHoc++;
                        }
                    }
                }

            }
        }

        private void btnMonHoc_Click(object sender, EventArgs e)
        {
            int CCMonHoc = 0;
            CertificateEntities context = new CertificateEntities();
            //thống kê chứng chỉ môn học
            foreach (MonHoc mh in context.MonHoc)
            {
                if ((int)cmbMonHoc.SelectedValue != 0)
                {
                    foreach (LopHoc lop in mh.LopHoc)
                    {
                        foreach (ChungChi cc in lop.ChungChi)
                        {
                            if (lop.ChungChi != null && lop.Id_MH.Equals((int)cmbMonHoc.SelectedValue)) CCMonHoc++;
                        }
                    }
                }


            }


        }

        private void btnGiangVien_Click(object sender, EventArgs e)
        {
            int CCGiangVien = 0;
            CertificateEntities context = new CertificateEntities();
            //thống kê số chứng chì giảng viên
            foreach (GiangVien gv in context.GiangVien)
            {
                if ((int)cmbGiangVien.SelectedValue != 0)
                {
                    foreach (LopHoc lop in gv.LopHoc)
                    {
                        foreach (ChungChi cc in lop.ChungChi)
                        {
                            if (lop.ChungChi != null && lop.Id_GV.Equals((int)cmbGiangVien.SelectedValue)) CCGiangVien++;
                        }
                    }
                } lblCCGiangVien.Text = ("Số Chứng Chỉ môn học là " + CCGiangVien.ToString());





            }


        }

        private void cmbNamHoc_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void button5_Click(object sender, EventArgs e)
        {
            CertificateEntities context = new CertificateEntities();
            lblXemChiTiet.Text = "";
            //thống kê số chứng chì giảng viên
            //List<int> lisChungChi = new List<int>();
            int max = 0;
            string MASSV, HoTen;
            foreach (SinhVien sv in context.SinhVien)
            {
                foreach (ChungChi cc in sv.ChungChi)
                {
                    if (sv.ChungChi.Count<ChungChi>() > max)
                    {
                        max = sv.ChungChi.Count<ChungChi>();
                        MASSV = sv.MaSV;
                        HoTen = sv.HoVaTen;

                    }
                }

            }
            foreach (SinhVien sv in context.SinhVien)
            {
                //foreach (ChungChi cc in sv.ChungChi)
                //{
                if (sv.ChungChi.Count<ChungChi>() == max)
                {

                    MASSV = sv.MaSV;
                    HoTen = sv.HoVaTen;
                    lblXemChiTiet.Text = lblXemChiTiet.Text + MASSV + " " + HoTen + " có " + max + " Chứng Chỉ " + "\r\n";

                }
                //}
            }


        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            int c;
            CertificateEntities CN = new CertificateEntities();
            //khoa hoc
            if ((int)cmbMonHoc.SelectedValue == 0 && (int)cmbNamHoc.SelectedValue == 0 && (int)cmbGiangVien.SelectedValue == 0 && (int)cmbKhoaHoc.SelectedValue != 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);
            }
            //Mon hoc
            if ((int)cmbMonHoc.SelectedValue != 0 && (int)cmbNamHoc.SelectedValue == 0 && (int)cmbGiangVien.SelectedValue == 0 && (int)cmbKhoaHoc.SelectedValue == 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }
            //Nam hoc
            if ((int)cmbMonHoc.SelectedValue == 0 && (int)cmbNamHoc.SelectedValue != 0 && (int)cmbGiangVien.SelectedValue == 0 && (int)cmbKhoaHoc.SelectedValue == 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_TG == (int)cmbNamHoc.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }
            //Giang Vien
            if ((int)cmbMonHoc.SelectedValue == 0 && (int)cmbNamHoc.SelectedValue == 0 && (int)cmbGiangVien.SelectedValue != 0 && (int)cmbKhoaHoc.SelectedValue == 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }
            //Mon Hoc ,Nam Hoc ,Giang Vien
            if ((int)cmbMonHoc.SelectedValue != 0 && (int)cmbNamHoc.SelectedValue != 0 && (int)cmbGiangVien.SelectedValue != 0 && (int)cmbKhoaHoc.SelectedValue == 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue && cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue && cc.LopHoc.Id_TG == (int)cmbNamHoc.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }
            //Mon Hoc ,Nam Hoc ,Khoa Hoc
            if ((int)cmbMonHoc.SelectedValue != 0 && (int)cmbNamHoc.SelectedValue != 0 && (int)cmbGiangVien.SelectedValue == 0 && (int)cmbKhoaHoc.SelectedValue != 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue && cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue && cc.LopHoc.Id_TG == (int)cmbNamHoc.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }
            //Mon Hoc ,GiangVien ,Khoa Hoc
            if ((int)cmbMonHoc.SelectedValue != 0 && (int)cmbNamHoc.SelectedValue == 0 && (int)cmbGiangVien.SelectedValue != 0 && (int)cmbKhoaHoc.SelectedValue != 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue && cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue && cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }
            //Nam Hoc ,GiangVien ,Khoa Hoc
            if ((int)cmbMonHoc.SelectedValue == 0 && (int)cmbNamHoc.SelectedValue != 0 && (int)cmbGiangVien.SelectedValue != 0 && (int)cmbKhoaHoc.SelectedValue != 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue && cc.LopHoc.Id_TG == (int)cmbNamHoc.SelectedValue && cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }// Mon Hoc , Nam Hoc 
            if ((int)cmbMonHoc.SelectedValue != 0 && (int)cmbNamHoc.SelectedValue != 0 && (int)cmbGiangVien.SelectedValue == 0 && (int)cmbKhoaHoc.SelectedValue == 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue && cc.LopHoc.Id_TG == (int)cmbNamHoc.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }// Mon Hoc , Giang Vien 
            if ((int)cmbMonHoc.SelectedValue != 0 && (int)cmbNamHoc.SelectedValue == 0 && (int)cmbGiangVien.SelectedValue != 0 && (int)cmbKhoaHoc.SelectedValue == 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue && cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }// Mon Hoc , Khoa Hoc 
            if ((int)cmbMonHoc.SelectedValue != 0 && (int)cmbNamHoc.SelectedValue == 0 && (int)cmbGiangVien.SelectedValue == 0 && (int)cmbKhoaHoc.SelectedValue != 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue && cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }
            //  Nam Hoc , giang vien 
            if ((int)cmbMonHoc.SelectedValue == 0 && (int)cmbNamHoc.SelectedValue != 0 && (int)cmbGiangVien.SelectedValue != 0 && (int)cmbKhoaHoc.SelectedValue == 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue && cc.LopHoc.Id_TG == (int)cmbNamHoc.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }
            //  Nam Hoc , Khoa Hoc 
            if ((int)cmbMonHoc.SelectedValue == 0 && (int)cmbNamHoc.SelectedValue != 0 && (int)cmbGiangVien.SelectedValue == 0 && (int)cmbKhoaHoc.SelectedValue != 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue && cc.LopHoc.Id_TG == (int)cmbNamHoc.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }
            //  Giang Vien , Khoa Hoc 
            if ((int)cmbMonHoc.SelectedValue == 0 && (int)cmbNamHoc.SelectedValue == 0 && (int)cmbGiangVien.SelectedValue != 0 && (int)cmbKhoaHoc.SelectedValue != 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue && cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            } //  Giang Vien , Khoa Hoc ,nam hoc ,mon hoc
            if ((int)cmbMonHoc.SelectedValue != 0 && (int)cmbNamHoc.SelectedValue != 0 && (int)cmbGiangVien.SelectedValue != 0 && (int)cmbKhoaHoc.SelectedValue != 0)
            {
                var CC = from p in CN.ChungChi.Where(cc => cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue && cc.LopHoc.Id_TG == (int)cmbNamHoc.SelectedValue && cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue && cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue) select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }//  Giang Vien , Khoa Hoc ,nam hoc ,mon hoc
            if ((int)cmbMonHoc.SelectedValue == 0 && (int)cmbNamHoc.SelectedValue == 0 && (int)cmbGiangVien.SelectedValue == 0 && (int)cmbKhoaHoc.SelectedValue == 0)
            {
                var CC = from p in CN.ChungChi select p;
                c = CC.Count<ChungChi>();
                lblCCGiangVien.Text = ("số chứng chỉ " + c);

            }




            //CC = from n in CN.ChungChi.Where(cc => cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue && cc.LopHoc.Id_KH ==(int)cmbKhoaHoc.SelectedValue ) select n;
            //CC = from l in CN.ChungChi.Where(cc => cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue || cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue || cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue) select l;
            //CC = from m in CN.ChungChi.Where(cc => cc.LopHoc.Id_TG == (int)cmbHocKy.SelectedValue || cc.LopHoc.Id_MH == (int)cmbMonHoc.SelectedValue || cc.LopHoc.Id_GV == (int)cmbGiangVien.SelectedValue || cc.LopHoc.Id_KH == (int)cmbKhoaHoc.SelectedValue) select m;
            //lblCCGiangVien.Text = ("so chung chi " + CC.Count<ChungChi>());
        }

        private void lblCCGiangVien_Click(object sender, EventArgs e)
        {

        }

        private void button7_Click(object sender, EventArgs e)
        {

            CertificateEntities CN = new CertificateEntities();
            int tim = (int)cmbSoCCCuaSV.SelectedValue;
            int i = 1;
            lblXemChiTiet.Text = "Các Chứng chỉ SV đạt được : " + "\r\n";


            foreach (SinhVien sv in CN.SinhVien)
            {
                if (sv.Id.Equals(tim))
                {
                    foreach (ChungChi cc in sv.ChungChi)
                    {
                        foreach (MonHoc mh in CN.MonHoc) {
                            foreach (LopHoc lh in mh.LopHoc) {
                                if (sv.Id == cc.Id_SV && lh.Id==cc.Id_LH)
                                {
                                    lblXemChiTiet.Text = lblXemChiTiet.Text +i+"      "+ mh.TenMH.ToString() + "\r\n";
                                    i++;
                                }
                            }
                        }
                        

                    }
                }

            }
            



        }

        private void button8_Click(object sender, EventArgs e)
        {
            CertificateEntities context = new CertificateEntities();
            //thống kê số chứng chì giảng viên
            List<int> lisChungChi = new List<int>();
            int Tim = (int)nudSoSVCoCC.Value;
            string MASSV, HoTen;
            lblXemChiTiet.Text = "";
            foreach (SinhVien sv in context.SinhVien)
            {
                if (sv.ChungChi.Count<ChungChi>() == Tim)
                {
                    Tim = sv.ChungChi.Count<ChungChi>();
                    MASSV = sv.MaSV;
                    HoTen = sv.HoVaTen;
                }

            }
            foreach (SinhVien sv in context.SinhVien)
            {

                if (sv.ChungChi.Count<ChungChi>() == Tim)
                {
                    MASSV = sv.MaSV;
                    HoTen = sv.HoVaTen;
                    lblXemChiTiet.Text = lblXemChiTiet.Text + MASSV + " " + HoTen + " có " + Tim + " Chứng Chỉ " + "\r\n";

                }
            }

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}
